package com.qq.financeSystem.mapper;

import com.qq.financeSystem.model.Offset;
import com.qq.financeSystem.model.YearAndMonth;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;

@Mapper
@Repository
public interface OffsetMapper {

    //查询某人本月报销总额(薪资系统调用)
    @Select("SELECT SUM(offset_money)as offset_money from offset " +
            "where emp_id=#{employeeId} and DATE_FORMAT(time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public Double selectSumMoneyById(String employeeId);

    //查询本月个人报销详情（薪资系统调用的）
    @Select("SELECT * FROM offset where emp_id=#{employeeId}" +
            " and DATE_FORMAT(time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public List<Offset> selectAllOffsetById(String employeeId);

    //查询指定年月个人报销详情（薪资系统调用的）
    @Select("SELECT * FROM offset where emp_id=#{employeeId}" +
            " and year(offset.time)=#{year} and month(offset.time)=#{month}")
    public List<Offset> selectLastOffsetById(YearAndMonth key);

    //查询本月全部人员报销信息(主页展示的)
    @Select("SELECT * from `offset` where identificationCode = #{ident} and DATE_FORMAT(time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public List<Offset> selectAllOffset(String ident);

    //查询指定年月全部信息报销（历史查询展示的）
    @Select("select * from offset where  year(offset.time)=#{year} "+
            "and month(offset.time)=#{month} and identificationCode=#{identificationCode}")
    public List<Offset> selectOffsetByKey(YearAndMonth key);

    //添加报销数据
    @Insert("insert into offset values(null,#{empName},#{empId},#{identificationCode},#{matters},#{offsetMoney},#{time})")
    public int insertOffset(Offset offset);

    //删除报销数据
    @Delete("delete offset where offsetId=#{offsetId}")
    public int deleteOneById(String offsetId);

    //个人历全部史津贴记录
    @Select("select * from offset where emp_id=#{empId}")
    public List<Offset> selectLastOneById(String empId);

    //添加查询该人是否存在编制并返回部门识别码
    @Select("select identificationCode from employee_information where employeeId=#{empId} and name=#{empName}")
    public String selectOneByIdAndName(String empId, String empName);

    //查询本月某部门补贴总额(财务分析调用)
    @Select("SELECT IFNULL(SUM(offset_money),0)as offset_money from offset where identificationCode = #{ident} " +
            "and DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n")
    public BigDecimal selectMonthOffsetMoneyByIdent(String ident);
}
